﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace DAL
{
    public class DonDatHangQuery:DataProvide
    {
        //public List<DonDatHangDAL> LayDanhSachDonDatHang()
        //{
        //    List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
        //    SqlConnection chuoiKetNoi = null;
        //    try
        //    {
        //        base.Connect();
        //        chuoiKetNoi = connection;
        //        string sqlString = String.Format(@"SELECT * FROM DonDatHang");

        //        SqlDataReader dr = ExecuteReader(sqlString);
        //        while (dr.Read())
        //        {
        //            DonDatHangDAL donhang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
        //            dsDonHang.Add(donhang);
        //        }
        //    }/*
        //    catch
        //    {
        //        return null;
        //    }*/
        //    finally
        //    {
        //        if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
        //            chuoiKetNoi.Close();
        //    }
        //    return dsDonHang;
        //}
        
        //Thu hien lam
        public List<DonDatHangDAL> LayDSDonHang()
        {
            List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM DonDatHang ");
                //string sqlString = String.Format(@"SELECT * FROM DonDatHang WHERE TrangThaiGiaoHang = '" + 1 + "'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    DonDatHangDAL donHang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
                    dsDonHang.Add(donHang);

                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsDonHang;
        }
        //lay ds don hang chua lien lac
        public List<DonDatHangDAL> LayDSDonHangChuaLienLac()
        {
            List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                // string sqlString = String.Format(@"SELECT * FROM DonDatHang ");
                string sqlString = String.Format(@"SELECT * FROM DonDatHang WHERE TrangThaiGiaoHang = -1");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    DonDatHangDAL donHang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
                    dsDonHang.Add(donHang);

                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsDonHang;
        }
        public List<DonDatHangDAL> LayDSDonHangTheoTinhTrang(int ts)
        {
            List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM DonDatHang WHERE TrangThaiGiaoHang = '" + ts + "'");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    DonDatHangDAL donHang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
                    dsDonHang.Add(donHang);

                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsDonHang;
        }


        //lay danh sach don hang theo sap xep
        public List<DonDatHangDAL> LayDSDonHangTheoSapXep(string giatri)
        {
            List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM DonDatHang order by " + giatri + "  ");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    DonDatHangDAL donHang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
                    dsDonHang.Add(donHang);

                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsDonHang;
        }

        public List<DonDatHangDAL> LayDSDonHangTheoThoiGian(DateTime tuNgay, DateTime denNgay)
        {
            List<DonDatHangDAL> dsDonHang = new List<DonDatHangDAL>();
            SqlConnection chuoiKetNoi = null;
            try
            {
                base.Connect();
                chuoiKetNoi = connection;
                string sqlString = String.Format(@"SELECT * FROM DonDatHang WHERE NgayDat between '" + tuNgay + "' and '" + denNgay + "' ");

                SqlDataReader dr = ExecuteReader(sqlString);
                while (dr.Read())
                {
                    DonDatHangDAL donHang = (DonDatHangDAL)GetDataRowFromDataReader(dr);
                    dsDonHang.Add(donHang);

                }
            }/*
            catch
            {
                return null;
            }*/
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsDonHang;
        }

        //xoa don dat hang
        public static bool XoaDonDatHang(string maDDH)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                
                string insertCommand = "delete from   DonDatHang  WHERE	MaDonDatHang ='" + maDDH + "'";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();

               
            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        //them don dat hang
        public bool ThemDonDatHang(DonDatHangDAL dh)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "INSERT INTO DonDatHang(HoTenKH,DiaChi,Email,SoDienThoai,NgayDat,TongTien,TrangThaiGiaoHang) VALUES(N'" +
                    dh.HoTenKH + "', N'" +
                    dh.DiaChi + "', N'" +
                    dh.Email + "', N'" +
                    dh.SoDienThoai + "', '" +
                    dh.NgayDat + "', " +
                    dh.TongTien + ", " +                    
                    dh.TrangThaiGiaoHang + ")";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }

        //cap nhat don dat hang
        public bool CapNhatDonDatHang(DonDatHangDAL dh)
        {
            bool ketQua = true;
            try
            {
                base.Connect();

                string sqlString = "UPDATE DonDatHang" +
                    " SET HoTenKH = N'" + dh.HoTenKH + "', " +
                    " DiaChi = N'" + dh.DiaChi + "', " +
                    " Email = N'" + dh.Email + "', " +
                    " SoDienThoai = N'" + dh.SoDienThoai + "', " +
                    " NgayDat = '" + dh.NgayDat + "', " +
                    " TongTien = " + dh.TongTien + ", " +                    
                    " TrangThaiGiaoHang = " + dh.TrangThaiGiaoHang +
                    " WHERE MaDonDatHang = " + dh.MaDonDatHang + "";

                ExecuteNonQuery(sqlString);
            }/*
            catch (Exception ex)
            {
                ketQua = false;
            }*/
            finally
            {
                if (connection != null && connection.State == System.Data.ConnectionState.Open)
                    Disconnect();
            }
            return ketQua;
        }
        protected override object GetDataRowFromDataReader(SqlDataReader dr)
        {
            DonDatHangDAL donhang = new DonDatHangDAL();
            donhang.MaDonDatHang= dr.GetInt32(0);
            if (!dr.IsDBNull(1))
                donhang.HoTenKH = dr.GetString(1);
            if (!dr.IsDBNull(2))
                donhang.DiaChi = dr.GetString(2);
            if (!dr.IsDBNull(3))
                donhang.Email = dr.GetString(3);
            if (!dr.IsDBNull(4))
                donhang.SoDienThoai = dr.GetString(4);
            if (!dr.IsDBNull(5))
                donhang.NgayDat = Convert.ToDateTime(dr.GetDateTime(5).ToString());
            if (!dr.IsDBNull(6))
                donhang.TongTien = float.Parse(dr.GetDouble(6).ToString());
            if (!dr.IsDBNull(7))
                donhang.TrangThaiGiaoHang = dr.GetInt32(7);
            return (object)donhang;

                
        }
    }
}
